"""
宏观经济数据访问层 (DAO)
提供宏观经济相关数据的专用访问接口
"""

from typing import List, Optional, Dict, Any, Union
from datetime import datetime, date, timedelta
from sqlalchemy import select, and_, or_, desc, asc, func, extract
from sqlalchemy.ext.asyncio import AsyncSession

from .base import FinancialDataDAO
from ..models.macro import (
    MacroEconomicIndicator,
    MonetaryPolicy,
    FiscalData,
    TradeData,
    EnergyData,
    RealEstateData
)


class MacroEconomicIndicatorDAO(FinancialDataDAO[MacroEconomicIndicator]):
    """宏观经济指标数据访问层"""
    
    model = MacroEconomicIndicator
    
    async def get_by_indicator_and_date(
        self,
        session: AsyncSession,
        indicator_name: str,
        report_date: date
    ) -> Optional[MacroEconomicIndicator]:
        """获取指定指标指定日期的数据"""
        stmt = select(self.model).where(
            and_(
                self.model.indicator_name == indicator_name,
                self.model.report_date == report_date
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_indicator_history(
        self,
        session: AsyncSession,
        indicator_name: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        limit: int = 1000
    ) -> List[MacroEconomicIndicator]:
        """获取指标历史数据"""
        conditions = [self.model.indicator_name == indicator_name]
        
        if start_date:
            conditions.append(self.model.report_date >= start_date)
        if end_date:
            conditions.append(self.model.report_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_category(
        self,
        session: AsyncSession,
        category: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[MacroEconomicIndicator]:
        """根据分类获取指标数据"""
        conditions = [self.model.category == category]
        
        if start_date:
            conditions.append(self.model.report_date >= start_date)
        if end_date:
            conditions.append(self.model.report_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_country(
        self,
        session: AsyncSession,
        country: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[MacroEconomicIndicator]:
        """根据国家获取指标数据"""
        conditions = [self.model.country == country]
        
        if start_date:
            conditions.append(self.model.report_date >= start_date)
        if end_date:
            conditions.append(self.model.report_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_latest_indicators(
        self,
        session: AsyncSession,
        country: Optional[str] = None,
        category: Optional[str] = None,
        limit: int = 50
    ) -> List[MacroEconomicIndicator]:
        """获取最新指标数据"""
        conditions = []
        
        if country:
            conditions.append(self.model.country == country)
        if category:
            conditions.append(self.model.category == category)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions) if conditions else True)
            .order_by(desc(self.model.report_date))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_gdp_data(
        self,
        session: AsyncSession,
        country: str = 'China',
        years: int = 10
    ) -> List[MacroEconomicIndicator]:
        """获取GDP数据"""
        from_date = datetime.now().date() - timedelta(days=years * 365)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.country == country,
                    self.model.indicator_name.ilike('%GDP%'),
                    self.model.report_date >= from_date
                )
            )
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_cpi_data(
        self,
        session: AsyncSession,
        country: str = 'China',
        years: int = 5
    ) -> List[MacroEconomicIndicator]:
        """获取CPI数据"""
        from_date = datetime.now().date() - timedelta(days=years * 365)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.country == country,
                    self.model.indicator_name.ilike('%CPI%'),
                    self.model.report_date >= from_date
                )
            )
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_pmi_data(
        self,
        session: AsyncSession,
        country: str = 'China',
        years: int = 3
    ) -> List[MacroEconomicIndicator]:
        """获取PMI数据"""
        from_date = datetime.now().date() - timedelta(days=years * 365)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.country == country,
                    self.model.indicator_name.ilike('%PMI%'),
                    self.model.report_date >= from_date
                )
            )
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_indicator_growth(
        self,
        session: AsyncSession,
        indicator_name: str,
        country: str,
        periods: int = 12
    ) -> List[Dict[str, Any]]:
        """计算指标增长率"""
        data = await self.get_indicator_history(
            session, indicator_name, limit=periods + 1
        )
        
        if len(data) < 2:
            return []
        
        growth_data = []
        for i in range(len(data) - 1):
            current = data[i]
            previous = data[i + 1]
            
            if previous.indicator_value and previous.indicator_value != 0:
                growth_rate = (
                    (current.indicator_value - previous.indicator_value) / 
                    previous.indicator_value * 100
                )
            else:
                growth_rate = 0
            
            growth_data.append({
                'date': current.report_date,
                'value': current.indicator_value,
                'growth_rate': growth_rate,
                'period_type': 'month'  # 可以根据实际情况调整
            })
        
        return growth_data
    
    async def search_indicators(
        self,
        session: AsyncSession,
        keyword: str,
        country: Optional[str] = None,
        category: Optional[str] = None
    ) -> List[MacroEconomicIndicator]:
        """搜索指标"""
        conditions = [
            or_(
                self.model.indicator_name.ilike(f'%{keyword}%'),
                self.model.description.ilike(f'%{keyword}%')
            )
        ]
        
        if country:
            conditions.append(self.model.country == country)
        if category:
            conditions.append(self.model.category == category)
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())


class MonetaryPolicyDAO(FinancialDataDAO[MonetaryPolicy]):
    """货币政策数据访问层"""
    
    model = MonetaryPolicy
    
    async def get_by_country_and_date(
        self,
        session: AsyncSession,
        country: str,
        announcement_date: date
    ) -> List[MonetaryPolicy]:
        """获取指定国家指定日期的货币政策"""
        stmt = select(self.model).where(
            and_(
                self.model.country == country,
                self.model.announcement_date == announcement_date
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_interest_rate_history(
        self,
        session: AsyncSession,
        country: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[MonetaryPolicy]:
        """获取利率历史数据"""
        conditions = [
            self.model.country == country,
            self.model.policy_type == 'interest_rate'
        ]
        
        if start_date:
            conditions.append(self.model.announcement_date >= start_date)
        if end_date:
            conditions.append(self.model.announcement_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.announcement_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_policies(
        self,
        session: AsyncSession,
        country: str,
        days: int = 90
    ) -> List[MonetaryPolicy]:
        """获取最近的货币政策"""
        from_date = datetime.now().date() - timedelta(days=days)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.country == country,
                    self.model.announcement_date >= from_date
                )
            )
            .order_by(desc(self.model.announcement_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_policy_type(
        self,
        session: AsyncSession,
        policy_type: str,
        country: Optional[str] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[MonetaryPolicy]:
        """根据政策类型获取数据"""
        conditions = [self.model.policy_type == policy_type]
        
        if country:
            conditions.append(self.model.country == country)
        if start_date:
            conditions.append(self.model.announcement_date >= start_date)
        if end_date:
            conditions.append(self.model.announcement_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.announcement_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_current_interest_rate(
        self,
        session: AsyncSession,
        country: str
    ) -> Optional[MonetaryPolicy]:
        """获取当前利率"""
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.country == country,
                    self.model.policy_type == 'interest_rate'
                )
            )
            .order_by(desc(self.model.announcement_date))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def calculate_rate_changes(
        self,
        session: AsyncSession,
        country: str,
        periods: int = 12
    ) -> List[Dict[str, Any]]:
        """计算利率变化"""
        rate_history = await self.get_interest_rate_history(session, country)
        
        if len(rate_history) < 2:
            return []
        
        changes = []
        for i in range(min(periods, len(rate_history) - 1)):
            current = rate_history[i]
            previous = rate_history[i + 1]
            
            if current.current_value is not None and previous.current_value is not None:
                change = float(current.current_value) - float(previous.current_value)
                changes.append({
                    'date': current.announcement_date,
                    'current_rate': float(current.current_value),
                    'previous_rate': float(previous.current_value),
                    'change': change,
                    'change_type': 'increase' if change > 0 else 'decrease' if change < 0 else 'unchanged'
                })
        
        return changes


class FiscalDataDAO(FinancialDataDAO[FiscalData]):
    """财政数据访问层"""
    
    model = FiscalData
    
    async def get_by_country_and_year(
        self,
        session: AsyncSession,
        country: str,
        fiscal_year: int
    ) -> List[FiscalData]:
        """获取指定国家指定年度的财政政策"""
        stmt = select(self.model).where(
            and_(
                self.model.country == country,
                self.model.fiscal_year == fiscal_year
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_budget_data(
        self,
        session: AsyncSession,
        country: str,
        start_year: Optional[int] = None,
        end_year: Optional[int] = None
    ) -> List[FiscalData]:
        """获取预算数据"""
        conditions = [
            self.model.country == country,
            self.model.policy_type == 'budget'
        ]
        
        if start_year:
            conditions.append(self.model.fiscal_year >= start_year)
        if end_year:
            conditions.append(self.model.fiscal_year <= end_year)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.fiscal_year))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_tax_policies(
        self,
        session: AsyncSession,
        country: str,
        start_year: Optional[int] = None,
        end_year: Optional[int] = None
    ) -> List[FiscalData]:
        """获取税收政策"""
        conditions = [
            self.model.country == country,
            self.model.policy_type == 'tax'
        ]
        
        if start_year:
            conditions.append(self.model.fiscal_year >= start_year)
        if end_year:
            conditions.append(self.model.fiscal_year <= end_year)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.fiscal_year))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_debt_data(
        self,
        session: AsyncSession,
        country: str,
        years: int = 10
    ) -> List[FiscalData]:
        """获取债务数据"""
        current_year = datetime.now().year
        from_year = current_year - years
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.country == country,
                    self.model.policy_type == 'debt',
                    self.model.fiscal_year >= from_year
                )
            )
            .order_by(desc(self.model.fiscal_year))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_deficit_ratio(
        self,
        session: AsyncSession,
        country: str,
        fiscal_year: int
    ) -> Optional[float]:
        """计算财政赤字率"""
        budget_data = await self.get_by_country_and_year(session, country, fiscal_year)
        
        revenue = None
        expenditure = None
        gdp = None
        
        for policy in budget_data:
            if 'revenue' in policy.policy_name.lower():
                revenue = policy.amount
            elif 'expenditure' in policy.policy_name.lower():
                expenditure = policy.amount
            elif 'gdp' in policy.policy_name.lower():
                gdp = policy.amount
        
        if revenue and expenditure and gdp:
            deficit = float(expenditure) - float(revenue)
            deficit_ratio = (deficit / float(gdp)) * 100
            return deficit_ratio
        
        return None


class TradeDataDAO(FinancialDataDAO[TradeData]):
    """贸易数据访问层"""
    
    model = TradeData
    
    async def get_by_country_and_date(
        self,
        session: AsyncSession,
        country: str,
        trade_date: date
    ) -> List[TradeData]:
        """获取指定国家指定日期的贸易数据"""
        stmt = select(self.model).where(
            and_(
                self.model.country == country,
                self.model.trade_date == trade_date
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_trade_balance(
        self,
        session: AsyncSession,
        country: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[TradeData]:
        """获取贸易平衡数据"""
        conditions = [
            self.model.country == country,
            self.model.trade_type == 'balance'
        ]
        
        if start_date:
            conditions.append(self.model.trade_date >= start_date)
        if end_date:
            conditions.append(self.model.trade_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.trade_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_export_import_data(
        self,
        session: AsyncSession,
        country: str,
        trade_type: str,  # 'export' or 'import'
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[TradeData]:
        """获取进出口数据"""
        conditions = [
            self.model.country == country,
            self.model.trade_type == trade_type
        ]
        
        if start_date:
            conditions.append(self.model.trade_date >= start_date)
        if end_date:
            conditions.append(self.model.trade_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.trade_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_bilateral_trade(
        self,
        session: AsyncSession,
        country1: str,
        country2: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[TradeData]:
        """获取双边贸易数据"""
        conditions = [
            self.model.country == country1,
            self.model.partner_country == country2
        ]
        
        if start_date:
            conditions.append(self.model.trade_date >= start_date)
        if end_date:
            conditions.append(self.model.trade_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.trade_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_trade_growth(
        self,
        session: AsyncSession,
        country: str,
        trade_type: str,
        periods: int = 12
    ) -> List[Dict[str, Any]]:
        """计算贸易增长率"""
        trade_data = await self.get_export_import_data(
            session, country, trade_type, limit=periods + 1
        )
        
        if len(trade_data) < 2:
            return []
        
        growth_data = []
        for i in range(len(trade_data) - 1):
            current = trade_data[i]
            previous = trade_data[i + 1]
            
            if previous.trade_value and previous.trade_value != 0:
                growth_rate = (
                    (current.trade_value - previous.trade_value) / 
                    previous.trade_value * 100
                )
            else:
                growth_rate = 0
            
            growth_data.append({
                'date': current.trade_date,
                'value': current.trade_value,
                'growth_rate': growth_rate,
                'trade_type': trade_type
            })
        
        return growth_data


class EnergyDataDAO(FinancialDataDAO[EnergyData]):
    """能源数据访问层"""
    
    model = EnergyData
    
    async def get_by_country_and_date(
        self,
        session: AsyncSession,
        country: str,
        report_date: date
    ) -> List[EnergyData]:
        """获取指定国家指定日期的能源数据"""
        stmt = select(self.model).where(
            and_(
                self.model.country == country,
                self.model.report_date == report_date
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_energy_type(
        self,
        session: AsyncSession,
        energy_type: str,
        country: Optional[str] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[EnergyData]:
        """根据能源类型获取数据"""
        conditions = [self.model.energy_type == energy_type]
        
        if country:
            conditions.append(self.model.country == country)
        if start_date:
            conditions.append(self.model.report_date >= start_date)
        if end_date:
            conditions.append(self.model.report_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_production_data(
        self,
        session: AsyncSession,
        country: str,
        energy_type: Optional[str] = None,
        years: int = 5
    ) -> List[EnergyData]:
        """获取能源生产数据"""
        from_date = datetime.now().date() - timedelta(days=years * 365)
        conditions = [
            self.model.country == country,
            self.model.report_date >= from_date
        ]
        
        if energy_type:
            conditions.append(self.model.energy_type == energy_type)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_consumption_data(
        self,
        session: AsyncSession,
        country: str,
        energy_type: Optional[str] = None,
        years: int = 5
    ) -> List[EnergyData]:
        """获取能源消费数据"""
        from_date = datetime.now().date() - timedelta(days=years * 365)
        conditions = [
            self.model.country == country,
            self.model.report_date >= from_date
        ]
        
        if energy_type:
            conditions.append(self.model.energy_type == energy_type)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_energy_efficiency(
        self,
        session: AsyncSession,
        country: str,
        energy_type: str,
        periods: int = 12
    ) -> List[Dict[str, Any]]:
        """计算能源效率"""
        data = await self.get_by_energy_type(session, energy_type, country)
        
        if len(data) < periods:
            return []
        
        efficiency_data = []
        for energy_record in data[:periods]:
            # 这里可以根据实际需求计算能源效率指标
            efficiency_data.append({
                'date': energy_record.report_date,
                'energy_type': energy_type,
                'production': energy_record.production_volume,
                'consumption': energy_record.consumption_volume,
                'price': energy_record.price,
                'unit': energy_record.unit
            })
        
        return efficiency_data


class RealEstateDataDAO(FinancialDataDAO[RealEstateData]):
    """房地产数据访问层"""
    
    model = RealEstateData
    
    async def get_by_city_and_date(
        self,
        session: AsyncSession,
        city: str,
        report_date: date
    ) -> List[RealEstateData]:
        """获取指定城市指定日期的房地产数据"""
        stmt = select(self.model).where(
            and_(
                self.model.city == city,
                self.model.report_date == report_date
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_price_history(
        self,
        session: AsyncSession,
        city: str,
        property_type: Optional[str] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[RealEstateData]:
        """获取房价历史数据"""
        conditions = [self.model.city == city]
        
        if property_type:
            conditions.append(self.model.property_type == property_type)
        if start_date:
            conditions.append(self.model.report_date >= start_date)
        if end_date:
            conditions.append(self.model.report_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_sales_volume_data(
        self,
        session: AsyncSession,
        city: str,
        years: int = 3
    ) -> List[RealEstateData]:
        """获取销售量数据"""
        from_date = datetime.now().date() - timedelta(days=years * 365)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.city == city,
                    self.model.report_date >= from_date
                )
            )
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_property_type(
        self,
        session: AsyncSession,
        property_type: str,
        city: Optional[str] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None
    ) -> List[RealEstateData]:
        """根据房产类型获取数据"""
        conditions = [self.model.property_type == property_type]
        
        if city:
            conditions.append(self.model.city == city)
        if start_date:
            conditions.append(self.model.report_date >= start_date)
        if end_date:
            conditions.append(self.model.report_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.report_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_price_trends(
        self,
        session: AsyncSession,
        city: str,
        property_type: str,
        periods: int = 12
    ) -> List[Dict[str, Any]]:
        """计算房价趋势"""
        data = await self.get_price_history(session, city, property_type)
        
        if len(data) < 2:
            return []
        
        trend_data = []
        for i in range(min(periods, len(data) - 1)):
            current = data[i]
            previous = data[i + 1]
            
            if previous.average_price and previous.average_price != 0:
                price_change = (
                    (current.average_price - previous.average_price) /
                    previous.average_price * 100
                )
            else:
                price_change = 0
            
            trend_data.append({
                'date': current.report_date,
                'average_price': current.average_price,
                'price_change': price_change,
                'sales_volume': current.sales_volume,
                'property_type': property_type
            })
        
        return trend_data
    
    async def get_market_summary(
        self,
        session: AsyncSession,
        city: str,
        report_date: Optional[date] = None
    ) -> Dict[str, Any]:
        """获取市场摘要"""
        if not report_date:
            report_date = datetime.now().date()
        
        data = await self.get_by_city_and_date(session, city, report_date)
        
        if not data:
            return {
                'city': city,
                'date': report_date,
                'total_sales_volume': 0,
                'average_price': 0,
                'property_types': []
            }
        
        total_volume = sum(d.sales_volume or 0 for d in data)
        avg_price = sum(d.average_price or 0 for d in data) / len(data)
        property_types = list(set(d.property_type for d in data if d.property_type))
        
        return {
            'city': city,
            'date': report_date,
            'total_sales_volume': total_volume,
            'average_price': avg_price,
            'property_types': property_types
        }